package se.hh.lab.dao.impl;

import java.sql.Connection;


import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import se.hh.lab.dao.DaoException;
import se.hh.lab.dao.InstrumentsDao;
import se.hh.lab.domain.Instruments;

import se.hh.lab.jdbc.JdbcUtil;

public class InstrumentsDaoImpl implements InstrumentsDao
{

	@SuppressWarnings("finally")
	@Override
	public int updateInstruments(Instruments inst)
	{
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs =null;		
		int mark=0;
		try
		{
			con = JdbcUtil.getConnection();
			String sql="update lab_instrument set ifin=?,name=?,price=?,remark=? where barcode=?" ;
			ps = con.prepareStatement(sql);	
			ps.setString(1,inst.getType());
			ps.setString(2,inst.getName());
			ps.setString(3,inst.getPrice());
			ps.setString(4,inst.getRemark());
			ps.setString(5,inst.getBarcode());
			mark=ps.executeUpdate();			
		}	
		catch (Exception e)
		{
			throw new DaoException(e.getMessage(),e);
		}

		finally
		{
			try
			{
				JdbcUtil.free(rs, ps, con);
			}
			catch (SQLException e)
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}	
			return mark;
		}				
	}

	@SuppressWarnings("finally")
	@Override
	public ArrayList<Instruments> getInstruments(Instruments inst) 
	{
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs =null;		
		String sql=null;			
		ArrayList<Instruments> list=new ArrayList<Instruments>();
		try
		{				
			if(inst.getBarcode()!=null)
			{
				sql="select barcode,name,ifin,price,remark,date from lab_instrument" +
						" where barcode like '%"+inst.getBarcode()+"%'";
			}
			else if(inst.getName()!=null)
			{
				sql="select barcode,name,ifin,price,remark,date from lab_instrument" +
						" where name like '%"+inst.getName()+"%'";
			}
			else if(inst.getType()!=null)
			{
				sql="select barcode,name,ifin,price,remark,date from lab_instrument" +
						" where ifin='"+inst.getType()+"'";
				
			}
			
			con = JdbcUtil.getConnection();						
			ps = con.prepareStatement(sql);
			rs=ps.executeQuery();
			while (rs.next())
			{
				inst=new Instruments();
				inst.setBarcode(rs.getString("barcode"));
				inst.setName(rs.getString("name"));
				inst.setRemark(rs.getString("remark"));
				inst.setType(rs.getString("ifin"));
				inst.setPrice(rs.getString("price"));
				inst.setDate(rs.getDate("date"));
			//	rs.getDate(9);
				list.add(inst);
			}
		}	
		catch (Exception e)
		{
			throw new DaoException(e.getMessage(),e);
		}
		finally
		{
			try
			{
				JdbcUtil.free(rs, ps, con);
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}		
			return list;
		}				
	}

	
	public ArrayList<Instruments> getInstruments(String str,Integer page)
	{
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs =null;		
		Instruments inst=null;
		Integer pagesize=5;
		page-=1;
		String sql;
		Integer pagestart=page*pagesize;
		
			sql="select barcode,name,ifin,price,remark,date from lab_instrument " +
				" where barcode like '%"+str+"%' or name like '%"+str+"%' or ifin like '%"+str+"%' limit "+pagestart+","+pagesize+"" ;			

			ArrayList<Instruments> list=new ArrayList<Instruments>();
		try
		{				
			con = JdbcUtil.getConnection();						
			ps = con.prepareStatement(sql);
			rs=ps.executeQuery();
			
			while (rs.next())
			{
//				String adminID = rs.getString(1);
//				String adminName = rs.getString(2);
//				String sex = rs.getString(3);
//				String code = rs.getString(4);
//				System.out.println(adminID + " " +adminName + " "
//						+ sex + " "+code );
				inst=new Instruments();
				inst.setBarcode(rs.getString("barcode"));
				inst.setName(rs.getString("name"));
				inst.setPrice(rs.getString("price"));
				inst.setRemark(rs.getString("remark"));
				inst.setType(rs.getString("ifin"));
				inst.setDate(rs.getDate("date"));
				list.add(inst);
			}
		}	
		catch (Exception e)
		{
			throw new DaoException(e.getMessage(),e);
		}

		finally
		{
			try
			{
				JdbcUtil.free(rs, ps, con);
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}				
		}			
		return list;		

	}
	@SuppressWarnings("finally")
	@Override
	public ArrayList<Instruments> getAdvancedInstruments(Instruments inst,Integer page)
	{
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs =null;		
		String sql=null;	
		Integer pagesize=5;
		page-=1;
		Integer pagestart=page*pagesize;
		ArrayList<Instruments> list=new ArrayList<Instruments>();
		try
		{				
			if(!"".equals(inst.getBarcode())&& !"".equals(inst.getName()) && !"".equals(inst.getType()))
			{
				sql="select barcode,name,ifin,price,remark,date from lab_instrument" +
						" where barcode like '%"+inst.getBarcode()+"%' and " +
						"ifin like '%"+inst.getType()+"%' and name like '%"+inst.getName()+"%'limit "+pagestart+","+pagesize+" ";
			}
			else if(!"".equals(inst.getBarcode())&& !"".equals(inst.getName()) && "".equals(inst.getType()))
			{
				sql="select barcode,name,ifin,price,remark,date from lab_instrument" +
						" where barcode like '%"+inst.getBarcode()+"%' and " +
						" name like '%"+inst.getName()+"%' limit "+pagestart+","+pagesize+"";
			}
			else if(!"".equals(inst.getBarcode())&& "".equals(inst.getName()) && !"".equals(inst.getType()))
			{
				sql="select barcode,name,ifin,price,remark,date from lab_instrument" +
						" where barcode like '%"+inst.getBarcode()+"%' and " +
						"ifin like '%"+inst.getType()+"%' limit "+pagestart+","+pagesize+" ";
			}
			else if(!"".equals(inst.getBarcode())&& "".equals(inst.getName()) && "".equals(inst.getType()))
			{
				sql="select barcode,name,ifin,price,remark,date from lab_instrument" +
						" where barcode like '%"+inst.getBarcode()+"%' limit "+pagestart+","+pagesize+" ";
			}
			else if("".equals(inst.getBarcode())&& !"".equals(inst.getName()) && !"".equals(inst.getType()))
			{
				sql="select barcode,name,ifin,price,remark,date from lab_instrument" +
						" where ifin like '%"+inst.getType()+"%' and name like '%"+inst.getName()+"%' limit "+pagestart+","+pagesize+" ";
			}
			else if("".equals(inst.getBarcode())&& "".equals(inst.getName()) && !"".equals(inst.getType()))
			{
				sql="select barcode,name,ifin,price,remark,date from lab_instrument" +
						" where ifin ='"+inst.getType()+"' limit "+pagestart+","+pagesize+"";
			}
			else if("".equals(inst.getBarcode())&& !"".equals(inst.getName()) && "".equals(inst.getType()))
			{
				sql="select barcode,name,ifin,price,remark,date from lab_instrument" +
						" where  name like '%"+inst.getName()+"%'limit "+pagestart+","+pagesize+" ";
			}
			else
			{
				sql="select barcode,name,ifin,price,remark,date from lab_instrument limit "+pagestart+","+pagesize+"";
			}
			con = JdbcUtil.getConnection();						
			ps = con.prepareStatement(sql);
			rs=ps.executeQuery();
			while (rs.next())
			{
				inst=new Instruments();
				inst.setBarcode(rs.getString("barcode"));
				inst.setName(rs.getString("name"));
				inst.setRemark(rs.getString("remark"));
				inst.setType(rs.getString("ifin"));
				inst.setPrice(rs.getString("price"));
				inst.setDate(rs.getDate("date"));
			//	rs.getDate(9);
				list.add(inst);
			}
		}	
		catch (Exception e)
		{
			throw new DaoException(e.getMessage(),e);
		}
		finally
		{
			try
			{
				JdbcUtil.free(rs, ps, con);
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}		
			return list;
		}
		
	}

//	public void updateInstruments(String id,String inst) 
//	{
//		Connection con = null;
//		PreparedStatement ps = null;
//		ResultSet rs =null;		
//		try
//		{
//			con = JdbcUtil.getConnection();
//			String sql="update instrument set mark=? where barcode=?" ;
//			ps = con.prepareStatement(sql);	
//			ps.setString(1,inst);
//			ps.setString(2,id);
//			ps.executeUpdate();			
//		}	
//		catch (Exception e)
//		{
//			throw new DaoException(e.getMessage(),e);
//		}
//
//		finally
//		{
//			JdbcUtil.free(rs, ps, con);				
//		}			
//	}
		
}

